package com.zhiyou.app.shop.website.dao.impl;

import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Order;
import org.springframework.orm.hibernate4.HibernateCallback;
import org.springframework.stereotype.Component;

import com.zhiyou.app.shop.common.util.Page;
import com.zhiyou.app.shop.manager.dao.impl.BaseHibernateDAO;
import com.zhiyou.app.shop.website.dao.IPrizePayDao;
import com.zhiyou.app.shop.website.dao.pojo.PrizePay;

@Component
public class PrizePayDaoImpl extends BaseHibernateDAO<PrizePay, String> implements
		IPrizePayDao {
	
	public PrizePayDaoImpl(){
		super(PrizePay.class);
	}

	public List findPrizePay(final int start ,final int pagesize) {
		List list = hibernateTemplate.execute(new HibernateCallback<List>() {
			public List doInHibernate(Session session)
					throws HibernateException {
				String sql ="SELECT d.tdate,IFNULL(sum(d.push),0) push,IFNULL(sum(d.pairs),0) paird ,IFNULL(sum(d.leader),0) leader,IFNULL(sum(d.point),0) point,"+
							" IFNULL(sum(d.consumer),0) consumer,IFNULL(sum(d.love_fund),0) love_fund,sum(d.register_amount) register_amount FROM 	( SELECT date_format(t.create_date, '%Y-%m-%d') tdate,"+
							" sum(t.push_prize) push, sum(t.pairs_prize) pairs,	sum(t.leader_prize) leader,"+
							" sum(t.point_prize) point,	sum(t.consumer_prize) consumer,sum(t.love_fund) love_fund,t.register_amount from prize_pay t "+
							" group by t.create_date) d GROUP BY d.tdate order by d.tdate desc ";
				sql += " limit "+start+","+pagesize;
				Query query = session.createSQLQuery(sql);
				return query.list();
			}
		});
		return list;
	}

	public int findPrizePayCnt() {
		Integer list = hibernateTemplate.execute(new HibernateCallback<Integer>() {
			public Integer doInHibernate(Session session)
					throws HibernateException {
				String sql = "select count(1) cnt from (SELECT d.tdate,IFNULL(sum(d.push),0) push,	" +
						"IFNULL(sum(d.pairs),0) paird ,	IFNULL(sum(d.leader),0) leader,	IFNULL(sum(d.point),0) point, " +
						"IFNULL(sum(d.consumer),0) consumer,IFNULL(sum(d.love_fund),0) love_fund,sum(d.register_amount) register_amount FROM 	( SELECT date_format(t.create_date, '%Y-%m-%d') tdate, sum(t.push_prize) push, sum(t.pairs_prize) pairs,sum(t.leader_prize) leader, sum(t.point_prize) point,sum(t.consumer_prize) consumer,sum(t.love_fund) love_fund,	t.register_amount from prize_pay t  group by t.create_date) d GROUP BY d.tdate) cn";
				SQLQuery query = session.createSQLQuery(sql);
				return (Integer)query.addScalar("cnt", new org.hibernate.type.IntegerType()).uniqueResult();
			}
		});
		return list;
	}

	public List findPrize(final String account, final String level, final String startDate, final String endDate,
			final int start, final int pageSize) {
		List list = hibernateTemplate.execute(new HibernateCallback<List>() {
			public List doInHibernate(Session session)
					throws HibernateException {
				String sql = "select u.user_account,u.level,p.create_date, IFNULL(p.push_prize,0)," +
						"IFNULL(p.pairs_prize,0),IFNULL(p.leader_prize,0),IFNULL(p.point_prize,0),IFNULL(p.consumer_prize,0),IFNULL(p.love_fund,0) from prize_pay p,user u " +
						"where p.user_id=u.user_account ";
				if(account != null && !"".equals(account)){
					sql += " and  p.user_id like '%"+account+"%' ";
				}
				if(level != null && !"".equals(level)){
					sql += " and u.level = '"+level+"' ";
				}
				if((startDate != null && !"".equals(startDate)) && (endDate != null && !"".equals(endDate))){
					sql += " and p.create_date between "+Timestamp.valueOf(startDate)+" and "+Timestamp.valueOf(endDate);
				}
				sql += " order by p.create_date desc";
				sql += " limit "+start+","+pageSize;
				SQLQuery query = session.createSQLQuery(sql);
				return query.list();
			}
		});
		return list;
	}

	public int findprizeCnt(final String account, final String level,final String startDate, final String endDate) {
		Integer list = hibernateTemplate.execute(new HibernateCallback<Integer>() {
			public Integer doInHibernate(Session session)
					throws HibernateException {
				String sql = "select count(1) cnt from prize_pay p,user u " +
						"where p.user_id=u.user_account ";
				if(account != null && !"".equals(account)){
					sql += " and  p.user_id like '%"+account+"%' ";
				}
				if(level != null && !"".equals(level)){
					sql += " and u.level = '"+level+"' ";
				}
				if((startDate != null && !"".equals(startDate)) && (endDate != null && !"".equals(endDate))){
					sql += " and p.create_date between "+Timestamp.valueOf(startDate)+" and "+Timestamp.valueOf(endDate);
				}
				SQLQuery query = session.createSQLQuery(sql);
				return (Integer)query.addScalar("cnt", new org.hibernate.type.IntegerType()).uniqueResult();
			}
		});
		return list;
	}
}
